package Jdbc;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JDBC {
	public static void main1(String[] args) throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.cj.jdbc.Driver");
		String url = "jdbc:mysql://localhost:3306/test929";
		String name = "root";
		String password = "maihao1122";
		Connection connection = DriverManager.getConnection(url, name, password);
		System.out.println(connection);
	}

	public static void main2(String[] args) throws SQLException, ClassNotFoundException {
//		Dept dept = new Dept(120, "安保部", "新乡市");
//		add(dept);
		//Dept dept1 = new Dept(120, "施工部", "郑州市");
		//update(dept1);
		//del(120);
	}

	public static void main(String[] args) throws SQLException, ClassNotFoundException {
		//del(120);
		//SelOne(100);
		List<Dept> list=SelAll();
		list.forEach(System.out::println);
	}
	public static List<Dept> SelAll() throws ClassNotFoundException,SQLException {
		Class.forName("com.mysql.cj.jdbc.Driver");
		String url = "jdbc:mysql://localhost:3306/test929";
		String name = "root";
		String password = "maihao1122";
		Connection connection = DriverManager.getConnection(url, name, password);
		System.out.println(connection);

		String sql="select deptno,dname,loc from dept";
		PreparedStatement pstm=connection.prepareStatement(sql);
		List<Dept> list=new ArrayList<>();
		ResultSet resultSet= pstm.executeQuery();
		while(resultSet.next()){
			int deptno1 = resultSet.getInt(1);
			String dname = resultSet.getString(2);
			String loc = resultSet.getString(3);
			Dept dept = new Dept(deptno1, dname, loc);
			list.add(dept);
		}
		pstm.close();
		connection.close();
		return list;
	}
	public static Dept SelOne(int deptno) throws ClassNotFoundException,SQLException{
		Class.forName("com.mysql.cj.jdbc.Driver");
		String url = "jdbc:mysql://localhost:3306/test929";
		String name = "root";
		String password = "maihao1122";
		Connection connection = DriverManager.getConnection(url, name, password);
		System.out.println(connection);

		String sql="select deptno,dname,loc from dept where deptno =?";
		PreparedStatement pstm=connection.prepareStatement(sql);
		pstm.setInt(1,deptno);
		ResultSet resultSet=pstm.executeQuery();
			if (resultSet.next()) {
				int deptno1 = resultSet.getInt(1);
				String dname = resultSet.getString(2);
				String loc = resultSet.getString(3);
				Dept dept = new Dept(deptno1, dname, loc);
				System.out.println("查到:" + dept);
				return dept;
			}
		pstm.close();
		connection.close();
		return null;
	}

	public static void add(Dept dept) throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.cj.jdbc.Driver");
		String url = "jdbc:mysql://localhost:3306/test929";
		String name = "root";
		String password = "maihao1122";
		Connection connection = DriverManager.getConnection(url, name, password);
		System.out.println(connection);

		String sql = "insert into dept(deptno,dname,loc) values(?,?,?)";
		PreparedStatement pstm = connection.prepareStatement(sql);
		pstm.setInt(1, dept.getDeptno());
		pstm.setString(2, dept.getDname());
		pstm.setString(3, dept.getLoc());
		int i = pstm.executeUpdate();
		System.out.println("添加了" + i + "条记记录");
		pstm.close();
		connection.close();
	}

	public static void update(Dept dept) throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.cj.jdbc.Driver");
		String url = "jdbc:mysql://localhost:3306/test929";
		String name = "root";
		String password = "maihao1122";
		Connection connection = DriverManager.getConnection(url, name, password);
		System.out.println(connection);

		String sql = "update  dept set dname=?,loc=? where deptno=?";
		PreparedStatement pstm = connection.prepareStatement(sql);
		pstm.setString(1, dept.getDname());
		pstm.setString(2, dept.getLoc());
		pstm.setInt(3, dept.getDeptno());

		int i = pstm.executeUpdate();
		System.out.println("添加了" + i + "条记记录");
		pstm.close();
		connection.close();
	}
	public static void del(Integer deptno) throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.cj.jdbc.Driver");
		String url = "jdbc:mysql://localhost:3306/test929";
		String name = "root";
		String password = "maihao1122";
		Connection connection = DriverManager.getConnection(url, name, password);
		System.out.println(connection);

		String sql = "delete from dept where deptno=?";
		PreparedStatement pstm = connection.prepareStatement(sql);
		pstm.setInt(1, deptno);

		int i = pstm.executeUpdate();
		System.out.println("添加了" + i + "条记记录");
		pstm.close();
		connection.close();
	}
}
